/*
 * 创建日期 2009-11-29
 *
 * 成都天和软件公司
 * 电话：028-85425861
 * 传真：028-85425861-8008
 * 邮编：610041 
 * 版权所有
 */
package  org.fantasy.common.db.sqlserver;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import  org.fantasy.common.util.DateUtil;

/**
 *  功能说明
 * @author: 王文成
 * @version: 1.0
 * @since 2009-11-29
 */
public class SqlServerUtils {

	public static final String COUNT = "cnt";

	private static final Log log = LogFactory.getLog(SqlServerUtils.class);

	/**
	 * 取得运行时SQL
	 * 
	 * @return
	 */
	public static String getRuntimeSql(String sql, List values) {
		for (int i = 0; i < values.size(); i++) {
			Object value = values.get(i);
			if (value instanceof Number) {
				sql = StringUtils.replaceOnce(sql, "?", value.toString());
			} else if (value instanceof String) {
				sql = StringUtils.replaceOnce(sql, "?", "'" + value.toString() + "'");
			} else if (value instanceof java.util.Date) {
				String dateString = DateUtil.format((java.util.Date) value, "yyyy-MM-dd HH:mm:ss");
				sql = StringUtils.replaceOnce(sql, "?", "to_date('" + dateString + "','YYYY-MM-DD HH24:MI:SS'");
			} else {
				sql = StringUtils.replaceOnce(sql, "?", "'" + String.valueOf(value) + "'");
			}
		}
		return sql;
	}

	/**
	 * 分页SQL
	 * 
	 * @param pageStart
	 * @param pageSize
	 * @return
	 */
	public static String getPagedSql(String sql, int pageStart, int pageSize) {
		StringBuilder temp = new StringBuilder(512);
		temp.append("SELECT TOP " + pageSize + " * \n");
		temp.append("FROM (" + sql + ") AS A \n");
		temp.append("WHERE (A.ID >= \n");
		temp.append("          (SELECT MAX(id) \n");
		temp.append("         FROM (SELECT TOP " + pageStart + " B.id \n");
		temp.append("                 FROM (" + sql + ") AS B \n");
		temp.append("                 ORDER BY B.id) AS T))  \n");
		temp.append("ORDER BY A.ID \n");
		return temp.toString();
	}

	/**
	 * 分页SQL
	 * 
	 * @param pageStart
	 * @param pageSize
	 * @return
	 */
	public static String getTopRowsSql(String sql, int rows) {
		StringBuilder temp = new StringBuilder(512);
		int i = sql.toLowerCase().indexOf("select");
		temp.append("select top " + rows + "");
		temp.append(sql.substring(i + "select".length()));
		return temp.toString();
	}

	/**
	 * 求总数SQL
	 * 
	 * @param pageStart
	 * @param pageSize
	 * @return
	 */
	public static String getCountSql(String sql) {
		StringBuffer temp = new StringBuffer(sql.length() + 128);
		temp.append(" SELECT count(*) as cnt FROM (\n ");
		temp.append(sql);
		temp.append(" \n)");
		return temp.toString();
	}

	/**
	 * 求总数SQL
	 * 
	 * @param pageStart
	 * @param pageSize
	 * @return
	 */
	public static String getCountSql(String sql, String type) {
		StringBuffer temp = new StringBuffer(sql.length() + 128);
		temp.append(" SELECT count(A.ID) " + SqlServerUtils.COUNT + " FROM (\n ");
		temp.append(sql);
		temp.append(" \n) A");
		return temp.toString();
	}

	/**
	 * 关闭所有
	 * 
	 * @param conn
	 * @param stmt
	 * @param rs
	 */
	public static void closeAll(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException ex) {
				log.error(ex);
			}
		}

		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException ex) {
				log.error(ex);
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException ex) {
				log.error(ex);
			}
		}
	}
}
